﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Collections;
using System.Data.SqlClient;
using System.Text;
using IDAL;
using Models;

namespace DAL
{
    public class AboutAndCareerPicDAL:IAboutAndCareerPic
    {
        public DataBase DB = new DataBase();
        public const string SQL_ABOUT_PIC = @"select * from AboutCareerPicInfo where CategoryID = 1 ORDER BY PicLevel DESC ";
        public const string SQL_CAREER_PIC = @"select * from AboutCareerPicInfo where CategoryID = 2 ORDER BY PicLevel DESC ";
        //public ArrayList GetAboutPicList()
        //{
        //    ArrayList list = new ArrayList();
        //    DB.DatabaseConnection();
        //    SqlDataReader reader = DB.GetdataRecords(SQL_ABOUT_PIC);
        //    while (reader.Read())
        //    {
        //        list.Add(reader["PicURL"].ToString());
        //    }
        //    DB.CloseDB();
        //    return list;
        //}
        //public ArrayList GetCareerPicList()
        //{
        //    ArrayList list = new ArrayList();
        //    DB.DatabaseConnection();
        //    SqlDataReader reader = DB.GetdataRecords(SQL_CAREER_PIC);
        //    while (reader.Read())
        //    {
        //        list.Add(reader["PicURL"].ToString());
        //    }
        //    DB.CloseDB();
        //    return list;
        //}
        public List<AboutAndCareerPic> GetCareerPicList()
        {
            List<AboutAndCareerPic> list = new List<AboutAndCareerPic>();
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(SQL_CAREER_PIC);
            while (reader.Read())
            {
                AboutAndCareerPic pic = new AboutAndCareerPic();
                ReadTo(reader, pic);
                list.Add(pic);
            }
            DB.CloseDB();
            return list;
        }
        public List<AboutAndCareerPic> GetAboutPicList()
        {
            List<AboutAndCareerPic> list = new List<AboutAndCareerPic>();
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(SQL_ABOUT_PIC);
            while (reader.Read())
            {
                AboutAndCareerPic pic = new AboutAndCareerPic();
                ReadTo(reader, pic);
                list.Add(pic);
            }
            DB.CloseDB();
            return list;
        }
        private void ReadTo(SqlDataReader reader,AboutAndCareerPic pic)
        {
            pic.PicID = reader["PicID"] is DBNull ? 0 : Int32.Parse(reader["PicID"].ToString());
            pic.PicURL = reader["PicURL"] is DBNull ? "" : reader["PicURL"].ToString();
            pic.CategoryID = reader["CategoryID"] is DBNull ? 0 : Int32.Parse(reader["CategoryID"].ToString());
            pic.PicLevel = reader["PicLevel"] is DBNull ? 0 : Int32.Parse(reader["PicLevel"].ToString());
            pic.PicName = reader["PicName"] is DBNull ? "" : reader["PicName"].ToString();
        }
        public List<AboutAndCareerPic> GetPicList(int id)
        {
            List<AboutAndCareerPic> piclist = new List<AboutAndCareerPic>();
            string sql = "SELECT * FROM AboutCareerPicInfo WHERE CategoryID = " + id;
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(sql);
            while (reader.Read())
            {
                AboutAndCareerPic pic = new AboutAndCareerPic();
                ReadTo(reader,pic);
                piclist.Add(pic);
            }
            DB.CloseDB();
            return piclist;
        }
        public AboutAndCareerPic GetPicByID(int ID)
        {
            AboutAndCareerPic pic = new AboutAndCareerPic();
            string sql = "SELECT * FROM AboutCareerPicInfo WHERE PicID = " + ID;
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(sql);
            while (reader.Read())
            {
                ReadTo(reader, pic);
            }
            DB.CloseDB();
            return pic;
        }
        public int InsertAboutAndCareerPic(AboutAndCareerPic pic)
        {
            string sql = "INSERT INTO AboutCareerPicInfo([CategoryID],[PicURL],[PicLevel],[PicName]) VALUES(" + pic.CategoryID + ",'" + pic.PicURL + "'," + pic.PicLevel + ",'" + pic.PicName + "');";
            int isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }
        public int UpdateAboutAndCareerPic(AboutAndCareerPic pic)
        {
            string sql = @"UPDATE AboutCareerPicInfo SET 
                CategoryID = " + pic.CategoryID + @",
                PicURL = '" + pic.PicURL + @"',
                PicName = '" + pic.PicName + @"',
                PicLevel = " + pic.PicLevel + " WHERE PicID = " + pic.PicID;

            return DB.UpdateRecords(sql);
        }
        public int DeleteAboutAndCareerPic(AboutAndCareerPic pic)
        {
            string sql = "DELETE FROM AboutCareerPicInfo WHERE PicID = " + pic.PicID;
            int isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }
        public List<AboutAndCareerPic> GetAboutPics()
        {
            return this.GetPicList(1);
        }
        public List<AboutAndCareerPic> GetCareerPics()
        {
            return this.GetPicList(2);
        }
    }
}
